package fon.rm2.server.rs232;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;


import com.mysql.jdbc.Statement;

public class DatabaseConnectionClass {

	public DatabaseConnectionClass(){
		getConnection();
	}
	
	public static Connection getConnection(){
		Connection conn = null;
		  try
		    {
		      Class.forName("com.mysql.jdbc.Driver"); 
		      conn = DriverManager.getConnection("jdbc:mysql:///connection",
		    	        "root", "office");
		    }
		    catch (Exception e)
		    {
		      System.err.println("Got an exception! "); 
		      System.err.println(e.getMessage()); 
		    } 
		  
		return conn;
	}
	
	public boolean checkUser(String user) throws SQLException{
		Connection conn = getConnection();
		Statement stmt = (Statement) conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT user_ID FROM user");
		while (rs.next()) {
			String result = rs.getString("user_ID");
			if (result.equalsIgnoreCase(user)){
				rs.close();
				stmt.close();
				conn.close();
				return true;
			}
		}
		rs.close();
		stmt.close();
		conn.close();
		return false;
	}
	
	public void writeHistory(String user, boolean allowed) throws SQLException{
		Calendar cal = Calendar.getInstance();
		java.sql.Date date =  new java.sql.Date( cal.getTime().getTime() );
		java.sql.Time time = new java.sql.Time( cal.getTime().getTime() );
		
		int a;
		if(allowed) a = 1; else a = 0;
		
		Connection conn = getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "INSERT INTO history (user_ID, date, time, status) VALUES  ('" + user + "','" + date + "','"+ time +"','"+ a +"') ";
		stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		
	}
	
	public static ArrayList<HistoryRecord> readHistory(String user, String dateFrom, String dateTo) throws SQLException, ParseException{
		
		ArrayList<HistoryRecord> history = new ArrayList<HistoryRecord>();
        
	    
		Connection conn = getConnection();
		Statement stmt = (Statement) conn.createStatement();
		ResultSet rs =null;
		
		if(dateFrom.equals("")&&dateTo.equals("")){//show all records for user
			rs = stmt.executeQuery("SELECT user_ID, date, time, status FROM history where user_ID = '"+user+"' ");
		}else{
			SimpleDateFormat sdfSource = new SimpleDateFormat("dd/MM/yy");
			Date date = sdfSource.parse(dateFrom);
			Date date2 = sdfSource.parse(dateTo);
		    SimpleDateFormat sdfDestination = new SimpleDateFormat("yyyy-MM-dd");
		    dateFrom = sdfDestination.format(date);
		    dateTo = sdfDestination.format(date2);
			
			
			rs = stmt.executeQuery("SELECT user_ID, date, time, status FROM history where user_ID = '"+user+"' and date BETWEEN '"+dateFrom+"' AND '"+dateTo+"' ");
			System.out.println("SELECT user_ID, date, time, status FROM history where user_ID = '"+user+"' and date BETWEEN '"+dateFrom+"' AND '"+dateTo+"' ");
		}
		
		while (rs.next()) {
			HistoryRecord record = new HistoryRecord();
			record.setID( rs.getString("user_ID"));
			record.setDate( rs.getString("date"));
			record.setTime( rs.getString("time"));
			record.setStatus( rs.getString("status"));
			history.add(record);
		}
		return history;
	}
	
	public static class HistoryRecord{
		public String ID;
		public String date;
		public String time;
		public String status;
		
		public String getID() {
			return ID;
		}

		public void setID(String iD) {
			ID = iD;
		}

		public String getDate() {
			return date;
		}

		public void setDate(String date) {
			this.date = date;
		}

		public String getTime() {
			return time;
		}

		public void setTime(String time) {
			this.time = time;
		}

		public String getStatus() {
			return status;
		}

		public void setStatus(String status) {
			this.status = status;
		}

		public HistoryRecord(){}
		
		public HistoryRecord(String id, String date, String time, String status){
			this.ID = id;
			this.date = date;
			this.time = time;
			this.status = status;
		}
	}
		
}


